﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using H2.NET.DBUtility;
namespace H2.NET.Web.Management.Config.Service
{
    /// <summary>
    /// 数据访问类:t_smd_Config
    /// </summary>
    public partial class ConfigService
    {
        public ConfigService()
        {
            DbHelperSQL.connectionString = PubConstant.GetConnectionString("smdDB");
        }
        #region  BasicMethod
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int ConfigID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from t_smd_Config");
            strSql.Append(" where ConfigID=@ConfigID");
            SqlParameter[] parameters = {
					new SqlParameter("@ConfigID", SqlDbType.Int,4)
			};
            parameters[0].Value = ConfigID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(H2.NET.Web.Management.Config.Model.t_smd_Config model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into t_smd_Config(");
            strSql.Append("Code,Name,Value,Seq,Level,ParentID)");
            strSql.Append(" values (");
            strSql.Append("@Code,@Name,@Value,@Seq,@Level,@ParentID)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@Code", SqlDbType.VarChar,50),
					new SqlParameter("@Name", SqlDbType.VarChar,50),
					new SqlParameter("@Value", SqlDbType.VarChar,50),
					new SqlParameter("@Seq", SqlDbType.Int,4),
					new SqlParameter("@ParentID", SqlDbType.Int,4),
                    new SqlParameter("@Level",SqlDbType.Int,4)};
            parameters[0].Value = model.Code;
            parameters[1].Value = model.Name;
            parameters[2].Value = model.Value;
            parameters[3].Value = model.Seq;
            parameters[4].Value = model.ParentID;
            parameters[5].Value = model.Level;
            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(H2.NET.Web.Management.Config.Model.t_smd_Config model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update t_smd_Config set ");
            strSql.Append("Code=@Code,");
            strSql.Append("Name=@Name,");
            strSql.Append("Value=@Value,");
            strSql.Append("Seq=@Seq,");
            strSql.Append("ParentID=@ParentID,");
            strSql.Append("Level=@Level");
            strSql.Append(" where ConfigID=@ConfigID");
            SqlParameter[] parameters = {
					new SqlParameter("@Code", SqlDbType.VarChar,50),
					new SqlParameter("@Name", SqlDbType.VarChar,50),
					new SqlParameter("@Value", SqlDbType.VarChar,50),
					new SqlParameter("@Seq", SqlDbType.Int,4),
					new SqlParameter("@ParentID", SqlDbType.Int,4),
					new SqlParameter("@ConfigID", SqlDbType.Int,4),
                    new SqlParameter("@Level", SqlDbType.Int,4)};
            parameters[0].Value = model.Code;
            parameters[1].Value = model.Name;
            parameters[2].Value = model.Value;
            parameters[3].Value = model.Seq;
            parameters[4].Value = model.ParentID;
            parameters[5].Value = model.ConfigID;
            parameters[6].Value = model.Level;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool BatchUpdate(string sql )
        {
            var rows = DbHelperSQL.ExecuteSql(sql);
            return rows > 0;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int ConfigID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from t_smd_Config ");
            strSql.Append(" where ConfigID=@ConfigID");
            SqlParameter[] parameters = {
					new SqlParameter("@ConfigID", SqlDbType.Int,4)
			};
            parameters[0].Value = ConfigID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string ConfigIDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from t_smd_Config ");
            strSql.Append(" where ConfigID in (" + ConfigIDlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        //根据城市获取城市类型
        public string getCityClassByCityName(string cityName)
        {
            DataTable dt_cityClass = GetList("ParentId = (select configid from t_smd_Config where name = '" + cityName + "' and level = 3) order by seq").Tables[0];

            if (dt_cityClass != null && dt_cityClass.Rows.Count > 0)
            {
                return dt_cityClass.Rows[0]["name"].ToString().ToUpper();
            }
            return "";
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public H2.NET.Web.Management.Config.Model.t_smd_Config GetModel(int ConfigID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 ConfigID,Code,Name,Value,Seq,ParentID,Level from t_smd_Config ");
            strSql.Append(" where ConfigID=@ConfigID");
            SqlParameter[] parameters = {
					new SqlParameter("@ConfigID", SqlDbType.Int,4)
			};
            parameters[0].Value = ConfigID;

            H2.NET.Web.Management.Config.Model.t_smd_Config model = new H2.NET.Web.Management.Config.Model.t_smd_Config();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public H2.NET.Web.Management.Config.Model.t_smd_Config DataRowToModel(DataRow row)
        {
            H2.NET.Web.Management.Config.Model.t_smd_Config model = new H2.NET.Web.Management.Config.Model.t_smd_Config();
            if (row != null)
            {
                if (row["ConfigID"] != null && row["ConfigID"].ToString() != "")
                {
                    model.ConfigID = int.Parse(row["ConfigID"].ToString());
                }
                if (row["Code"] != null)
                {
                    model.Code = row["Code"].ToString();
                }
                if (row["Name"] != null)
                {
                    model.Name = row["Name"].ToString();
                }
                if (row["Value"] != null)
                {
                    model.Value = row["Value"].ToString();
                }
                if (row["Seq"] != null && row["Seq"].ToString() != "")
                {
                    model.Seq = int.Parse(row["Seq"].ToString());
                }
                if (row["Level"] != null && row["Level"].ToString() != "")
                {
                    model.Level = int.Parse(row["Level"].ToString());
                }
                if (row["ParentID"] != null && row["ParentID"].ToString() != "")
                {
                    model.ParentID = int.Parse(row["ParentID"].ToString());
                }
            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ConfigID,Code,Name,Value,Seq,Level,ParentID ");
            strSql.Append(" FROM t_smd_Config ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" ConfigID,Code,Name,Value,Seq,Level,ParentID ");
            strSql.Append(" FROM t_smd_Config ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM t_smd_Config ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.ConfigID desc");
            }
            strSql.Append(")AS Row, T.*  from t_smd_Config T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }
        #endregion  BasicMethod
        #region  ExtensionMethod
        /// <summary>
        /// 根据id删除所有子节点
        /// </summary>
        public void deleteAllChild(int id)
        {
            string ids = getAllChild("", id);
            string[] pIds = ids.Split(',');
            for (int i = pIds.Length - 2; i >= 0; i--)
            {
                int j = int.Parse(pIds[i]);
                Delete(j);
            }
            Delete(id);

        }

        public string getAllChild(String ids, int id)
        {

            if (getChild(id) != null)
            {
                string[] pIds = getChild(id).ToString().Split(',');

                for (int i = 0; i < pIds.Length - 1; i++)
                {
                    ids += pIds[i] + ",";
                    ids = getAllChild(ids, int.Parse(pIds[i]));
                }
            }

            return ids;

        }

        public StringBuilder getChild(int id)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder ids = null;
            strSql.Append("select configid from t_smd_Config ");
            if (id != 0)
            {
                strSql.Append(" where ParentId=" + id);
            }
            //parameters[0].Value = id;

            DataSet rows = DbHelperSQL.Query(strSql.ToString());

            if (rows != null && rows.Tables[0] != null && rows.Tables[0].Rows != null && rows.Tables[0].Rows.Count > 0)
            {
                ids = new StringBuilder();
                for (int i = 0; i < rows.Tables[0].Rows.Count; i++)
                {
                    String pid = rows.Tables[0].Rows[i]["ConfigId"].ToString();
                    ids.Append(pid + ",");
                }
            }
            return ids;
        }
        #endregion  ExtensionMethod
    }
}

